package com.intellif.mozping;


import com.intellif.mozping.entity.Entity;
import com.intellif.mozping.entity.TimeRange;
import com.intellif.mozping.util.Configuration;
import com.intellif.mozping.util.Constant;
import com.intellif.mozping.util.DateUtil;
import com.intellif.mozping.util.Util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.CountDownLatch;

public class Test {

    private static Connection conn = null;
    private static final String EXPLAIN_STR = "EXPLAIN ANALYZE ";
    public static Map<String, TimeRange> partionTimeRange = new HashMap<>();

    private static ArrayList<Long> times = new ArrayList<>(100);
    private static int concurrence = 1;
    private static final CountDownLatch latch = new CountDownLatch(concurrence);
    private static int testNum = 1;
    private static ArrayList<Long> timeRaw = new ArrayList<>(100);
    private static ArrayList<Long> timesAfter = new ArrayList<>(100);

    //初始相关信息
    public static void init() throws Exception {
        //1.初始化连接
        if (conn == null) {
            conn = Util.getConn(Constant.GP_URL);
        }
        partionTimeRange = Util.initPartionsInfo(DateUtil.convertStringToDate("2019-06-25"),
                DateUtil.convertStringToDate("2020-06-25"), 4, false);

    }

    public static void main(String[] args) throws Exception {
        String mode = "dataConsistency1";
        init();
        if (Configuration.MODE.equals(mode)) {
            dataConsistency();
        } else {
            performanceTest();
        }
    }

    /**
     * 数据一致性测试
     */
    private static void dataConsistency() throws Exception {
        long begin = System.currentTimeMillis();
        int success = 0;
        for (int i = 1; i <= testNum; i++) {
            boolean result = doDataConsistency();
            if (result) {
                success++;
            }
            System.out.println("=============>>> 第 " + i + " 次查询..,结果是：" + result);

        }
        System.out.println("-------------------------测试次数：" + testNum + " , 成功次数：" + success);

        long totalRaw = 0;
        long totalAfter = 0;
        for (long time : timeRaw) {
            totalRaw += time;
        }
        for (long time : timesAfter) {
            totalAfter += time;
        }
        System.out.println("没有优化平均耗时：" + (totalRaw / timeRaw.size()) + " ms");
        System.out.println("优化后平均耗时：" + (totalAfter / timesAfter.size()) + " ms");
        System.out.println("程序执行耗时：" + (System.currentTimeMillis() - begin) + " ms");
    }

    /**
     * 性能测试
     */
    private static void performanceTest() throws Exception {
        for (int i = 0; i < concurrence; i++) {
            new Thread(new Runnable() {
                @Override
                public void run() {
                    try {
                        doPerformanceTest();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }).start();
        }
        latch.await();

        long total = 0;
        for (long time : timesAfter) {
            total += time;
        }
        System.out.println("Size is :" + times.size() + " ,Average Time is : " + (total / concurrence) + " ms");
    }


    /**
     * 原始查询
     */
    private static ArrayList<Object> queryRaw(boolean transforResult) throws Exception {
        //1.初始化连接
        if (conn == null) {
            conn = Util.getConn(Constant.GP_URL);
        }
        // 通过原始语句，查询记录
        long begin1 = System.currentTimeMillis();
        //ArrayList<Object> rawResult = queryFromDb(getSqlRaw(), false, transforResult);
        ArrayList<Object> rawResult = queryFromDb(getSqlRawMultiArg(5), false, transforResult);
        System.out.println("原始查询区间:" + DateUtil.getDateStr(Configuration.QUERY_BEGIN) +
                " -> " + DateUtil.getDateStr(Configuration.QUERY_END));
        long time1 = System.currentTimeMillis() - begin1;
        System.out.println("原始查询耗时: " + time1 + " ms");
        timeRaw.add(time1);
        return rawResult;
    }

    /**
     * 优化查询
     */
    private static ArrayList<Object> queryGood(boolean transforResult) throws Exception {
        long begin = System.currentTimeMillis();
        //ArrayList<Object> partions = queryFromDb(getSqlExplain(), true, false);
        //ArrayList<Object> partions = queryFromDb(getSqlExplainMultiArg(5), true, false);
        ArrayList<Object> partions = queryFromDb(getSqlExplain(true), true, false);

        //4.得到执行计划中几个分区拼接的时间范围
        TimeRange timeRange = getTimeRangeFromSomePartion(partions);
        System.out.println("查询的分区时间为：" + DateUtil.getDateStr(timeRange.getBegin()) + " -> " + DateUtil.getDateStr(timeRange.getEnd()));

        //4.1 分区的时间要和查询时间做交集，去最窄的时间范围
        TimeRange timeRangeResult = Util.getTimeRangeFromTwo(timeRange,
                new TimeRange(Configuration.QUERY_BEGIN, Configuration.QUERY_END));
        System.out.println("最终查询的时间范围：" + DateUtil.getDateStr(timeRangeResult.getBegin()) + " -> " + DateUtil.getDateStr(timeRangeResult.getEnd()));

        //5.到得到的时间范围去查询记录，并且order by
        //ArrayList<Object> result = queryFromDb(getSql(timeRangeResult.getBegin(), timeRangeResult.getEnd()), false, transforResult);
        //ArrayList<Object> result = queryFromDb(getSqlMultiArg(timeRangeResult.getBegin(), timeRangeResult.getEnd(), 5), false, transforResult);
        ArrayList<Object> result = queryFromDb(getSql(timeRangeResult.getBegin(), timeRangeResult.getEnd(), true), false, transforResult);

        long time2 = System.currentTimeMillis() - begin;
        System.out.println("优化查询耗时: " + time2 + " ms");
        timesAfter.add(time2);
        latch.countDown();
        return result;
    }

    private static boolean doDataConsistency() throws Exception {

        //1.原始查询
        ArrayList<Object> rawResult = queryRaw(true);
        //2.获取优化查询结果
        ArrayList<Object> result = queryGood(true);
        //3.对比2次查询的结果，相同返回true，不同返回false
        return compareResult(rawResult, result);
    }

    /**
     * 对比2个结果集，相同返回true，不同返回false(相同的条件是记录相同且位置也相同)
     */
    private static boolean compareResult(ArrayList<Object> rawResult, ArrayList<Object> result) {
        int total = rawResult.size();
        int same = 0;
        for (Object str : rawResult) {
            if (result.contains(str)) {
                same++;
            }
        }
        int sameWithIndex = 0;
        for (int i = 0; i < total; i++) {
            Entity e1 = (Entity) rawResult.get(i);
            Entity e2 = (Entity) result.get(i);
            if (e1.getTid().equals(e2.getTid()) && e1.getSourceId().equals(e2.getSourceId())) {
                sameWithIndex++;
            }
        }
        System.out.println("总记录数量是: " + total + " , 相同的记录数量是：" + same + " 对应位置相同的记录数量是: " + sameWithIndex);
        return sameWithIndex == total;
    }

    /**
     * 性能测试方法，性能测试只需要查询优化方法
     */
    private static void doPerformanceTest() throws Exception {
        queryGood(false);
    }


    /**
     * 原始查询的sql
     */
    private static String getSqlRaw(boolean isLike) {
        if (isLike) {
            return "select * from  " + Configuration.TABLE_NAME +
                    " where plateNumber like  '" + Configuration.PLATE_NUMBER +
                    "'and   pdate between'" + Configuration.QUERY_BEGIN + "' and '" + Configuration.QUERY_END +
                    "' order by pdate asc,   tid asc limit " +
                    Configuration.LIMIT_NUM;
        }
        return "select * from  " + Configuration.TABLE_NAME +
                " where color=  '" + Configuration.QUERY_COLOR +
                "'and   pdate between'" + Configuration.QUERY_BEGIN + "' and '" + Configuration.QUERY_END +
                "' order by pdate asc,   tid asc limit " +
                Configuration.LIMIT_NUM;
    }

    /**
     * 获取执行计划的sql - 单属性查询
     */
    private static String getSqlExplain(boolean isLike) {
        if (isLike) {
            return "select * from  " + Configuration.TABLE_NAME +
                    " where plateNumber like  '" + Configuration.PLATE_NUMBER +
                    "'and pdate between'" + Configuration.QUERY_BEGIN + "' and '" + Configuration.QUERY_END
                    + "'limit   " + Configuration.LIMIT_NUM;
        }
        return "select * from  " + Configuration.TABLE_NAME +
                " where color=  '" + Configuration.QUERY_COLOR +
                "'and pdate between'" + Configuration.QUERY_BEGIN + "' and '" + Configuration.QUERY_END
                + "'limit   " + Configuration.LIMIT_NUM;
    }

    /**
     * 优化后的sql - 单属性查询
     */
    static String getSql(Date begin, Date end, boolean isLike) {
        if (isLike) {
            return "select * from  " + Configuration.TABLE_NAME +
                    " where plateNumber like'" + Configuration.PLATE_NUMBER +
                    "'and pdate between'" + begin + "' and '" + end +
                    "'  order by pdate asc,   tid asc  limit  " + Configuration.LIMIT_NUM;
        }
        return "select * from  " + Configuration.TABLE_NAME +
                " where color=  '" + Configuration.QUERY_COLOR +
                "'and pdate between'" + begin + "' and '" + end +
                "'  order by pdate asc,   tid asc  limit   " + Configuration.LIMIT_NUM;
    }


    /**
     * 原始查询的sql- 多属性查询 - 参数代表属性个数
     */
    private static String getSqlRawMultiArg(int arg) {
        if (arg <= 3) {
            return "select * from  " + Configuration.TABLE_NAME +
                    " where color=  '" + Configuration.QUERY_COLOR +
                    "' and ( plateColor=  '" + Configuration.QUERY_COLOR +
                    "' or plateColor=  '" + Configuration.QUERY_PLATE_COLOR +
                    "' ) and ( carType=  '" + Configuration.QUERY_CAR_TYPE[0] +
                    "' or carType=  '" + Configuration.QUERY_CAR_TYPE[1] +
                    "' ) and pdate between'" + Configuration.QUERY_BEGIN + "' and '" + Configuration.QUERY_END
                    + "' order by pdate asc, tid asc limit " +
                    Configuration.LIMIT_NUM;
        }
        return "select * from  " + Configuration.TABLE_NAME +
                " where color=  '" + Configuration.QUERY_COLOR +
                "' and ( plateColor=  '" + Configuration.QUERY_COLOR +
                "' or plateColor=  '" + Configuration.QUERY_PLATE_COLOR +
                "' ) and ( carType=  '" + Configuration.QUERY_CAR_TYPE[0] +
                "' or carType=  '" + Configuration.QUERY_CAR_TYPE[1] +
                "' ) and ( brandCode  = '" + Configuration.BRAND_CODE +
                "' or plateNumber like  '" + Configuration.PLATE_NUMBER +
                "' ) and pdate between'" + Configuration.QUERY_BEGIN + "' and '" + Configuration.QUERY_END
                + "' order by pdate asc,  tid asc limit " +
                Configuration.LIMIT_NUM;
    }


    /**
     * 获取执行计划的sql - 多属性查询 - 参数代表属性个数
     */
    private static String getSqlExplainMultiArg(int arg) {
        if (arg <= 3) {
            return "select * from  " + Configuration.TABLE_NAME +
                    " where color=  '" + Configuration.QUERY_COLOR +
                    "' and ( plateColor=  '" + Configuration.QUERY_COLOR +
                    "' or plateColor=  '" + Configuration.QUERY_PLATE_COLOR +
                    "' ) and ( carType=  '" + Configuration.QUERY_CAR_TYPE[0] +
                    "' or carType=  '" + Configuration.QUERY_CAR_TYPE[1] +
                    "' ) and pdate between'" + Configuration.QUERY_BEGIN + "' and '" + Configuration.QUERY_END
                    + "'limit   " + Configuration.LIMIT_NUM;
        }
        return "select * from  " + Configuration.TABLE_NAME +
                " where color=  '" + Configuration.QUERY_COLOR +
                "' and ( plateColor=  '" + Configuration.QUERY_COLOR +
                "' or plateColor=  '" + Configuration.QUERY_PLATE_COLOR +
                "' ) and ( carType=  '" + Configuration.QUERY_CAR_TYPE[0] +
                "' or carType=  '" + Configuration.QUERY_CAR_TYPE[1] +
                "' ) and ( brandCode =  '" + Configuration.BRAND_CODE +
                "' or plateNumber like  '" + Configuration.PLATE_NUMBER +
                "' ) and pdate between'" + Configuration.QUERY_BEGIN + "' and '" + Configuration.QUERY_END
                + "'limit   " + Configuration.LIMIT_NUM;
    }

    /**
     * 优化后的sql - 多属性查询  - 参数代表属性个数
     */
    static String getSqlMultiArg(Date begin, Date end, int arg) {
        if (arg <= 3) {
            return "select * from  " + Configuration.TABLE_NAME +
                    " where color=  '" + Configuration.QUERY_COLOR +
                    "' and ( plateColor=  '" + Configuration.QUERY_COLOR +
                    "' or plateColor=  '" + Configuration.QUERY_PLATE_COLOR +
                    "' ) and ( carType=  '" + Configuration.QUERY_CAR_TYPE[0] +
                    "' or carType=  '" + Configuration.QUERY_CAR_TYPE[1] +
                    "' ) and pdate between'" + begin + "' and '" + end +
                    "'  order by pdate asc,  tid asc  limit   " + Configuration.LIMIT_NUM;
        }
        return "select * from  " + Configuration.TABLE_NAME +
                " where color=  '" + Configuration.QUERY_COLOR +
                "' and ( plateColor=  '" + Configuration.QUERY_COLOR +
                "' or plateColor=  '" + Configuration.QUERY_PLATE_COLOR +
                "' ) and ( carType=  '" + Configuration.QUERY_CAR_TYPE[0] +
                "' or carType=  '" + Configuration.QUERY_CAR_TYPE[1] +
                "' ) and ( brandCode =  '" + Configuration.BRAND_CODE +
                "' or plateNumber like  '" + Configuration.PLATE_NUMBER +
                "' ) and pdate between'" + begin + "' and '" + end +
                "'  order by pdate asc,  tid asc  limit   " + Configuration.LIMIT_NUM;
    }


    private static ArrayList<Object> queryFromDb(String sql, boolean explain, boolean transforResult) throws SQLException {
        ResultSet rs = null;
        ArrayList<Object> resultList;
        try {
            Statement statement = conn.createStatement();
            statement.execute("SET enable_seqscan = off");
            statement.execute("SET enable_indexscan = on");
            statement.execute("SET optimizer=off");
            if (explain) {
                //1.获取执行计划
                statement.execute(EXPLAIN_STR + sql);
                rs = statement.getResultSet();
                resultList = dealExplain(rs);
            } else {
                //1.根据分区的时间范围查询，并且order by
                statement.execute(sql);
                rs = statement.getResultSet();
                if (transforResult) {
                    resultList = Util.dealQueryResult(rs);
                } else {
                    int num = 0;
                    while (rs.next()) {
                        num++;
                    }
                    System.out.println("结果集条数是: " + num);
                    resultList = new ArrayList<>();
                }
            }
            return resultList;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    static ArrayList<Object> dealExplain(ResultSet resultSet) throws SQLException {
        ArrayList<Object> partions = new ArrayList<>();
        ArrayList<String> explainInfo = new ArrayList<>();
        //1.现将全部的执行计划信息封装到一个集合中
        while (resultSet.next()) {
            String executionPlan = resultSet.getSQLXML(1).getString();
            System.out.println(executionPlan);
            explainInfo.add(executionPlan);
        }

        //2.对集合进行遍历处理，找到Rows out大于0的全部分区名称
        for (int i = 0; i < explainInfo.size() - 3; i++) {
            String line = explainInfo.get(i);
            String lineRowsOut = explainInfo.get(i + 2);
            //包含表名称的行才有分区名称信息
            if (line.contains(Configuration.TABLE_NAME_SUFFIX) && !line.contains("index") && Util.rowsBigThanZero(lineRowsOut)) {
                //Rows out大于0才记录
                String pname = Util.getPartionName(line);
                partions.add(pname);
                System.out.println("查询计划扫描的分区: " + pname);
            }

        }
        return partions;
    }


    static TimeRange getTimeRangeFromSomePartion(ArrayList<Object> partionName) {
        Date begin = partionTimeRange.get(partionName.get(0)).getBegin();
        Date end = partionTimeRange.get(partionName.get(partionName.size() - 1)).getEnd();
        return new TimeRange(begin, end);
    }

}
